Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Tuning Rollback Segments

To properly configure a system’s rollback segments, you must create enough rollback segments and they must be of a sufficient size.

Number of Rollback Segments

The number of rollback segments should be determined by the number of concurrent transactions in the database. Remember, the fewer transactions per rollback segment, the less contention. A good rule of thumb is to create about one rollback segment for every four concurrent transactions.

Rollback contention occurs when too many transactions try to use the same rollback segment at the same time and some of them have to wait. You can tell whether you are seeing contention on rollback segments by looking at the dynamic performance table, V$WAITSTAT. V$WAITSTAT contains the following data related to rollback segments:

  UNDO HEADER: The number of waits for buffers containing rollback header blocks.
  UNDO BLOCK: The number of waits for buffers containing rollback blocks other than header blocks.
  SYSTEM UNDO HEADER: Same as UNDO HEADER for the SYSTEM rollback segment.
  SYSTEM UNDO BLOCK: Same as UNDO BLOCK for the SYSTEM rollback segment.

The system rollback segment is the original rollback segment created when the database was created. This rollback segment is used primarily for special system functions but is sometimes used when no other rollback segment is available. Typically, the system rollback segment is not used and you do not have to be concerned about it.

You can view these values with the following SQL statement:

SQL> SELECT class, count
  2  FROM V$WAITSTAT
  3  WHERE class IN
  4  ('undo header', 'undo block', 'system undo header', 'system undo block');

CLASS                  COUNT
------------------  --------
system undo header         0
system undo block          0
undo header                0
undo block                 0

Compare these values with the total number of requests for data. Remember (from earlier in the chapter) that the number of requests for data is equal to the sum of DB BUFFER GETS and CONSISTENT GETS from V$SYSSTAT. Also remember that you can extract that information with the following query:

SQL> SELECT SUM(value) "Data Requests"
  2  FROM v$sysstat
  3  WHERE name IN ('db block gets', 'consistent gets');

Data Requests
------------
         5105

If the number of waits for any of the rollback segment blocks or headers exceeds more than 1 percent of the total number of requests, you should reduce the contention by adding more rollback segments.

Size of Rollback Segments

Create several different sizes of rollback segments. Each type of rollback segment should be used by the application developer based on the type and length of the transaction:


Transaction Type Comments

OLTP OLTP transactions are characterized by many concurrent transactions, each modifying perhaps only a small amount of data. These types of transactions benefit from a reduction of contention and quick access from cached rollback segments. Try to create many small rollback segments of perhaps 10K to 20K in size, each with 2 to 4 extents—optimally with a rollback segment available for each transaction.
The small size of the rollback segments provides for a better chance of being cached in the SGA. There is probably very little dynamic growth of the extents.
Long Queries For long queries where read consistency calls for quite a bit of rollback information to be accessed, use a larger rollback segment. A good rule of thumb is to create rollback segments approximately 10 percent the size of the largest table (most SQL statements affect only about 10 percent of the data in a table).
Large Updates For transactions that update large amounts of data, you should also use a larger rollback segment. As is the case with the long queries, it is appropriate to create rollback segments approximately 10 percent the size of the largest table.

Size and Number of Extents

In general, the best rollback I/O performance can be obtained when there are approximately 10 to 20 extents of equal size per rollback segment. To determine the size and number of extents, use the following formula:

Rollback Segment Size = Rsize = Size of largest table / 10
Number of Extents = NE = 10
Size of Extents = Esize = Rsize / NE

When creating the rollback segments, use the value of Esize for INITIAL and NEXT; use the value of NE for MINEXTENTS. Even when using these rules, you may not achieve the most effective size for your rollback segments. If dynamic growth is occurring, you may be losing performance.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.